﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class EmployeeController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public string Save(Employee Employees)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateEmployee";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Employees.ID;
            command.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = Employees.FirstName;
            command.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = Employees.LastName;
            command.Parameters.Add("@DEPARTMENTID", SqlDbType.VarChar).Value = Employees.Department == null ? null : Employees.Department.ID.ToString();
            command.Parameters.Add("@SECTIONID", SqlDbType.VarChar).Value = Employees.Section == null ? null : Employees.Section.ID.ToString();
            command.Parameters.Add("@SUBSECTIONID", SqlDbType.VarChar).Value = Employees.SubSection == null ? null : Employees.SubSection.ID.ToString();
            command.Parameters.Add("@SUPERVISOR", SqlDbType.VarChar).Value = Employees.Supervisor.ToString().Trim() == "" ? 0 : Employees.Supervisor;// null Employees.Supervisor.ID.ToString();
            command.Parameters.Add("@JOBPOSITIONID", SqlDbType.VarChar).Value = Employees.JobPosition == null ? null : Employees.JobPosition.ID.ToString();
            command.Parameters.Add("@JOBTITLE", SqlDbType.VarChar).Value = Employees.JobTitle;
            command.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = Employees.Email;
            command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = Employees.SubBranch == null ? null : Employees.SubBranch.ID.ToString();
            command.Parameters.Add("@PHONE", SqlDbType.VarChar).Value = Employees.Phone;
            command.Parameters.Add("@EXT", SqlDbType.VarChar).Value = Employees.Ext;
            command.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = Employees.Password;
            command.Parameters.Add("@ISADMIN", SqlDbType.VarChar).Value = Employees.isAdmin;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = Employees.isActive;
            command.Parameters.Add("@PRIVILEGEID", SqlDbType.VarChar).Value = Employees.Privilege == null ? null : Employees.Privilege.ID.ToString();
            command.Parameters.Add("@JOINDATE", SqlDbType.Date).Value = Employees.JoinDate;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Employees.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = Employees.CreateBy;
            command.Parameters.Add("@ISLOCK", SqlDbType.VarChar).Value = Employees.isLock;
            command.Parameters.Add("@NIK", SqlDbType.VarChar).Value = Employees.NIK;

            return ExecuteNonQuery(command);
        }

        public bool Save(List<Employee> list)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    foreach (Employee Employees in list)
                    {

                        string query = "UP_InsertUpdateEmployee_IMPORT";
                        SqlCommand command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UP_InsertUpdateEmployee";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Employees.ID;
                        command.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = Employees.FirstName;
                        //command.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = Employees.LastName;
                        command.Parameters.Add("@DEPARTMENTID", SqlDbType.VarChar).Value = Employees.Department == null ? null : Employees.Department.ID.ToString();
                        command.Parameters.Add("@SECTIONID", SqlDbType.VarChar).Value = Employees.Section == null ? null : Employees.Section.ID.ToString();
                        command.Parameters.Add("@SUBSECTIONID", SqlDbType.VarChar).Value = Employees.SubSection == null ? null : Employees.SubSection.ID.ToString();
                        command.Parameters.Add("@SUPERVISOR", SqlDbType.VarChar).Value = Employees.Supervisor.ToString().Trim() == "" ? 0 : Employees.Supervisor;// null Employees.Supervisor.ID.ToString();
                        command.Parameters.Add("@JOBPOSITIONID", SqlDbType.VarChar).Value = Employees.JobPosition == null ? null : Employees.JobPosition.ID.ToString();
                        command.Parameters.Add("@JOBTITLE", SqlDbType.VarChar).Value = Employees.JobTitle;
                        command.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = Employees.Email;
                        command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = Employees.SubBranch == null ? null : Employees.SubBranch.ID.ToString();
                        //command.Parameters.Add("@PHONE", SqlDbType.VarChar).Value = Employees.Phone;
                        //command.Parameters.Add("@EXT", SqlDbType.VarChar).Value = Employees.Ext;
                        //command.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = Employees.Password;
                        //command.Parameters.Add("@ISADMIN", SqlDbType.VarChar).Value = Employees.isAdmin;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = Employees.isActive;
                        command.Parameters.Add("@PRIVILEGEID", SqlDbType.VarChar).Value = Employees.Privilege == null ? null : Employees.Privilege.ID.ToString();
                        //command.Parameters.Add("@JOINDATE", SqlDbType.Date).Value = Employees.JoinDate;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Employees.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = Employees.CreateBy;
                        //command.Parameters.Add("@ISLOCK", SqlDbType.VarChar).Value = Employees.isLock;
                        command.Parameters.Add("@NIK", SqlDbType.VarChar).Value = Employees.NIK;


                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public string Delete(Employee Employees)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_DeleteEmployee";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Employees.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = Employees.LastModifiedBy;

            return ExecuteNonQuery(command);
        }


        public string BeforeDelete(Employee Employees)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_CheckBeforeDeleteEmployee";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Employees.ID;
           

            return GetSingleData(command);
        }

        public string UpdatePassword(Employee Employees, string Password)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_UpdatePassword";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = Employees.ID;
            command.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = Password;

            return ExecuteNonQuery(command);
        }

        public IList<Employee> GetAll(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, int BusinessGroupID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllEmployees";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@BusinessGroupID", SqlDbType.VarChar).Value = BusinessGroupID;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "FirstName":
                            dx.Tables[0].DefaultView.Sort = "FirstName ASC";
                            break;
                        case "LastName":
                            dx.Tables[0].DefaultView.Sort = "LastName ASC";
                            break;
                        //case "SubBranch":
                        //    dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                        //    break;
                        case "Department":
                            dx.Tables[0].DefaultView.Sort = "DepartmentID ASC";
                            break;
                        //case "Section":
                        //    dx.Tables[0].DefaultView.Sort = "SectionID ASC";
                        //    break;
                        //case "SubSection":
                        //    dx.Tables[0].DefaultView.Sort = "SubSectionID ASC";
                        //    break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "JobPositionID ASC";
                            break;
                        //case "LastModifiedDate":
                        //    dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                        //    break;
                        //case "LastModifiedBy":
                        //    dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                        //    break;
                        //case "CreateDate":
                        //    dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                        //    break;
                        //case "CreateBy":
                        //    dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                        //    break;
                        case "NIK":
                            dx.Tables[0].DefaultView.Sort = "NIK ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "FirstName":
                            dx.Tables[0].DefaultView.Sort = "FirstName DESC";
                            break;
                        case "LastName":
                            dx.Tables[0].DefaultView.Sort = "LastName DESC";
                            break;
                        //case "SubBranch":
                        //    dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                        //    break;
                        case "Department":
                            dx.Tables[0].DefaultView.Sort = "DepartmentID DESC";
                            break;
                        //case "Section":
                        //    dx.Tables[0].DefaultView.Sort = "SectionID DESC";
                        //    break;
                        //case "SubSection":
                        //    dx.Tables[0].DefaultView.Sort = "SubSectionID DESC";
                        //    break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "JobPositionID DESC";
                            break;
                        //case "LastModifiedDate":
                        //    dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                        //    break;
                        //case "LastModifiedBy":
                        //    dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                        //    break;
                        //case "CreateDate":
                        //    dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                        //    break;
                        //case "CreateBy":
                        //    dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                        //    break;
                        case "NIK":
                            dx.Tables[0].DefaultView.Sort = "NIK DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();
                       
                        //p.Department = new DepartmentController().GetDepartmentByID(Convert.ToInt16(dt.Rows[i]["DepartmentID"].ToString()));
                        //p.Section = new SectionController().GetSectionByID(dt.Rows[i]["SectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SectionID"].ToString()));
                        //p.SubSection = new SubSectionController().GetSubSectionByID(dt.Rows[i]["SubSectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubSectionID"].ToString()));
                        p.Supervisor = Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString());// GetEmployeeByID(dt.Rows[i]["Supervisor"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString()));
                        //p.JobPosition = new JobPositionController().GetJobPositionByID(dt.Rows[i]["JobPositionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["JobPositionID"].ToString()));

                        p.Department = new Department();
                        p.Department.Description = dt.Rows[i]["DescriptionDept"].ToString();

                        p.SubBranch = new SubBranch();
                        p.SubBranch.Description = dt.Rows[i]["DescriptionSubBranch"].ToString();

                        p.Section = new Section();
                        p.Section.Description = dt.Rows[i]["DescriptionSection"].ToString();

                        p.JobPosition = new JobPosition();
                        p.JobPosition.Position = dt.Rows[i]["DescriptionJobPosition"].ToString();

                        p.SubSection = new SubSection();
                        p.SubSection.Description = dt.Rows[i]["DescriptionSubSection"].ToString();
                        

                        p.JobTitle = dt.Rows[i]["JobTitle"].ToString();
                        p.Email = dt.Rows[i]["Email"].ToString();
                        
                        //p.SubBranch = new SubBranchController().GetSubBranchByID(dt.Rows[i]["SubBranchID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubBranchID"].ToString()));
                        
                        p.Phone = dt.Rows[i]["Phone"].ToString();
                        p.Ext = dt.Rows[i]["Ext"].ToString();
                        p.Password = dt.Rows[i]["Password"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.isAdmin = Convert.ToBoolean(dt.Rows[i]["isAdmin"].ToString());
                        //p.Privilege = new PrivilegeController().GetPrivilegeByID(dt.Rows[i]["PrivilegeID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["PrivilegeID"].ToString()));
                        p.JoinDate = dt.Rows[i]["JoinDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["JoinDate"].ToString());
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.isLock = Convert.ToBoolean(dt.Rows[i]["isLock"].ToString());
                        p.NIK = dt.Rows[i]["NIK"].ToString();
                        Employees.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return Employees.ToList();
        }

        public IList<Employee> GetAllHierarchy(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, int CurrentUser)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "Employee_Hierarchy";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@CurrentUser", SqlDbType.VarChar).Value = CurrentUser;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "FirstName":
                            dx.Tables[0].DefaultView.Sort = "FirstName ASC";
                            break;
                        case "LastName":
                            dx.Tables[0].DefaultView.Sort = "LastName ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "Department":
                            dx.Tables[0].DefaultView.Sort = "DepartmentID ASC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "SectionID ASC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "SubSectionID ASC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "JobPositionID ASC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "FirstName":
                            dx.Tables[0].DefaultView.Sort = "FirstName DESC";
                            break;
                        case "LastName":
                            dx.Tables[0].DefaultView.Sort = "LastName DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "Department":
                            dx.Tables[0].DefaultView.Sort = "DepartmentID DESC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "SectionID DESC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "SubSectionID DESC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "JobPositionID DESC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();
                        p.Department = new DepartmentController().GetDepartmentByID(Convert.ToInt16(dt.Rows[i]["DepartmentID"].ToString()));
                        p.Section = new SectionController().GetSectionByID(dt.Rows[i]["SectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SectionID"].ToString()));
                        p.SubSection = new SubSectionController().GetSubSectionByID(dt.Rows[i]["SubSectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubSectionID"].ToString()));
                        p.Supervisor = Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString());// GetEmployeeByID(dt.Rows[i]["Supervisor"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString()));
                        p.JobPosition = new JobPositionController().GetJobPositionByID(dt.Rows[i]["JobPositionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["JobPositionID"].ToString()));
                        p.JobTitle = dt.Rows[i]["JobTitle"].ToString();
                        p.Email = dt.Rows[i]["Email"].ToString();
                        p.SubBranch = new SubBranchController().GetSubBranchByID(dt.Rows[i]["SubBranchID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubBranchID"].ToString()));
                        p.Phone = dt.Rows[i]["Phone"].ToString();
                        p.Ext = dt.Rows[i]["Ext"].ToString();
                        p.Password = dt.Rows[i]["Password"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.Privilege = new PrivilegeController().GetPrivilegeByID(dt.Rows[i]["PrivilegeID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["PrivilegeID"].ToString()));
                        p.JoinDate = dt.Rows[i]["JoinDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["JoinDate"].ToString());
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        Employees.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return Employees.ToList();
        }

        public IList<Employee> GetAllEmployeesByKPIAssign(string sSearch, int KPIAssignID, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllEmployeesByKPIAssignID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAssignID;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();
            
            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();
                        p.Department = new DepartmentController().GetDepartmentByID(Convert.ToInt16(dt.Rows[i]["DepartmentID"].ToString()));
                        p.Section = new SectionController().GetSectionByID(dt.Rows[i]["SectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SectionID"].ToString()));
                        p.SubSection = new SubSectionController().GetSubSectionByID(dt.Rows[i]["SubSectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubSectionID"].ToString()));
                        p.Supervisor = Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString());// GetEmployeeByID(dt.Rows[i]["Supervisor"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString()));
                        p.JobPosition = new JobPositionController().GetJobPositionByID(dt.Rows[i]["JobPositionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["JobPositionID"].ToString()));
                        p.JobTitle = dt.Rows[i]["JobTitle"].ToString();
                        p.Email = dt.Rows[i]["Email"].ToString();
                        p.SubBranch = new SubBranchController().GetSubBranchByID(dt.Rows[i]["SubBranchID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubBranchID"].ToString()));
                        p.Phone = dt.Rows[i]["Phone"].ToString();
                        p.Ext = dt.Rows[i]["Ext"].ToString();
                        p.Password = dt.Rows[i]["Password"].ToString();
                        p.isAdmin = Convert.ToBoolean(dt.Rows[i]["isAdmin"].ToString());
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.JoinDate = dt.Rows[i]["JoinDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["JoinDate"].ToString());
                        p.Privilege = new PrivilegeController().GetPrivilegeByID(dt.Rows[i]["PrivilegeID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["PrivilegeID"].ToString()));
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        Employees.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return Employees.ToList();
        }


        public IList<Employee> GetAllEmployeesByKPIAssignDtl(string sSearch, int KpiID, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeBy_AssignDtlID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Lokasi":
                            dx.Tables[0].DefaultView.Sort = "DescriptionBranch ASC";
                            break;
                        case "Nama":
                            dx.Tables[0].DefaultView.Sort = "FirstName ASC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment ASC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection ASC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection ASC";
                            break;
                        case "Jabatan":
                            dx.Tables[0].DefaultView.Sort = "Position ASC";
                            break;
                       
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Lokasi":
                            dx.Tables[0].DefaultView.Sort = "DescriptionBranch DESC";
                            break;
                        case "Nama":
                            dx.Tables[0].DefaultView.Sort = "FirstName DESC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment DESC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection DESC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection DESC";
                            break;
                        case "Jabatan":
                            dx.Tables[0].DefaultView.Sort = "Position DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();

                        p.SubBranch = new SubBranch();
                        p.SubBranch.Description = dt.Rows[i]["DescriptionSubBranch"].ToString();

                        p.SubBranch.Location = new Location();
                        p.SubBranch.Location.Description = dt.Rows[i]["DescriptionBranch"].ToString();

                        p.Department = new Department();
                        p.Department.Description = dt.Rows[i]["DescriptionDepartment"].ToString();


                        p.JobPosition = new JobPosition();
                        p.JobPosition.Position = dt.Rows[i]["Position"].ToString();

                        p.Section = new Section();
                        p.Section.Description = dt.Rows[i]["DescriptionSection"].ToString();

                        p.SubSection = new SubSection();
                        p.SubSection.Description = dt.Rows[i]["DescriptionSubSection"].ToString();

                        Employees.Add(p);

                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dt.Rows.Count;

            return Employees.ToList();
        }




        public Employee GetSpvEmployeeByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeByID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            Employee employees = null;

            if (Data != null && Data.Count > 0)
            {
                employees = new Employee();
                employees.ID = Convert.ToInt16(Data[0]);
                employees.FirstName = Data[1].ToString();
                employees.LastName = Data[2].ToString();
                employees.Department = new DepartmentController().GetDepartmentByID(Data[3] == string.Empty ? 0 : Convert.ToInt16(Data[3].ToString()));
                employees.Section = new SectionController().GetSectionByID(Data[4] == string.Empty ? 0 : Convert.ToInt16(Data[4].ToString()));
                employees.SubSection = new SubSectionController().GetSubSectionByID(Data[5] == string.Empty ? 0 : Convert.ToInt16(Data[5].ToString()));
                //if (ID != Convert.ToInt32(Data[6]))
                //employees.Supervisor = GetEmployeeByID(Data[6] == string.Empty ? 0 : Convert.ToInt16(Data[6].ToString()));
                //else
                //  employees.Supervisor = employees.Supervisor;
                employees.JobPosition = new JobPositionController().GetJobPositionByID(Data[7] == string.Empty ? 0 : Convert.ToInt16(Data[7].ToString()));
                employees.JobTitle = Data[8].ToString();
                employees.Email = Data[9].ToString();
                employees.SubBranch = new SubBranchController().GetSubBranchByID(Data[10] == string.Empty ? 0 : Convert.ToInt16(Data[10].ToString()));
                employees.Phone = Data[11].ToString();
                employees.Ext = Data[12].ToString();
                employees.Password = Data[13].ToString();
                employees.isAdmin = Convert.ToBoolean(Data[14]);
                employees.isActive = Convert.ToBoolean(Data[15]);
                employees.LastModifiedDate = Data[16] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[16].ToString());
                employees.LastModifiedBy = Data[17].ToString();
                employees.CreateDate = Data[18] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[18].ToString());
                employees.CreateBy = Data[19].ToString();
                employees.JoinDate = Data[20] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[20].ToString());
                employees.Privilege = new PrivilegeController().GetPrivilegeByID(Data[21] == string.Empty ? 0 : Convert.ToInt16(Data[21].ToString()));
            }

            return employees;
        }


        public Employee GetEmployeeByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeByID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            Employee employees = null;

            if (Data != null && Data.Count > 0)
            {
                employees = new Employee();
                employees.ID = Convert.ToInt16(Data[0]);
                employees.FirstName = Data[1].ToString();
                employees.LastName = Data[2].ToString();
                employees.Department = new DepartmentController().GetDepartmentByID(Data[3] == string.Empty ? 0 : Convert.ToInt16(Data[3].ToString()));
                employees.Section = new SectionController().GetSectionByID(Data[4] == string.Empty ? 0 : Convert.ToInt16(Data[4].ToString()));
                employees.SubSection = new SubSectionController().GetSubSectionByID(Data[5] == string.Empty ? 0 : Convert.ToInt16(Data[5].ToString()));

                //employees.Department = new Department();
                //employees.Department.ID = Data[3] == string.Empty ? 0 : Convert.ToInt16(Data[3].ToString());
                //employees.Department.Description = Data[23].ToString();
                //employees.Section = new Section();
                //employees.Section.ID = Data[4] == string.Empty ? 0 : Convert.ToInt16(Data[4].ToString());
                //employees.SubSection = new SubSection();
                //employees.SubSection.ID = Data[5] == string.Empty ? 0 : Convert.ToInt16(Data[5].ToString());

                
                employees.Supervisor = Convert.ToInt16(Data[6].ToString());//GetEmployeeByID(Data[6] == string.Empty ? 0 : Convert.ToInt16(Data[6].ToString()));
                
                employees.JobPosition = new JobPositionController().GetJobPositionByID(Data[7] == string.Empty ? 0 : Convert.ToInt16(Data[7].ToString()));
                //employees.JobPosition = new JobPosition();
                //employees.JobPosition.ID = Data[7] == string.Empty ? 0 : Convert.ToInt16(Data[7].ToString());
                
                employees.JobTitle = Data[8].ToString();
                employees.Email = Data[9].ToString();
                
                employees.SubBranch = new SubBranchController().GetSubBranchByID(Data[10] == string.Empty ? 0 : Convert.ToInt16(Data[10].ToString()));
                //employees.SubBranch = new SubBranch();
                //employees.SubBranch.ID = Data[10] == string.Empty ? 0 : Convert.ToInt16(Data[10].ToString());

                employees.Phone = Data[11].ToString();
                employees.Ext = Data[12].ToString();
                employees.Password = Data[13].ToString();
                employees.isAdmin = Convert.ToBoolean(Data[14]);
                employees.isActive = Convert.ToBoolean(Data[15]);
                employees.LastModifiedDate = Data[16] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[16].ToString());
                employees.LastModifiedBy = Data[17].ToString();
                employees.CreateDate = Data[18] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[18].ToString());
                employees.CreateBy = Data[19].ToString();
                employees.JoinDate = Data[20] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[20].ToString());
                
                employees.Privilege = new PrivilegeController().GetPrivilegeByID(Data[21] == string.Empty ? 0 : Convert.ToInt16(Data[21].ToString()));
                employees.BusinessGroupID = Convert.ToInt16(Data[22].ToString());
                employees.isLock = Convert.ToBoolean(Data[29]);
                employees.NIK = Data[30].ToString();
                //employees.Privilege = new Privilege();
                //employees.Privilege.ID = Data[21] == string.Empty ? 0 : Convert.ToInt16(Data[21].ToString());
                //
                //employees.Privilege.isActive = Data[24].ToString().Trim() == "True" ? true : false;
                //employees.Privilege.isAddKPI = Data[25].ToString().Trim() == "True" ? true : false;
                //employees.Privilege.isAssignee = Data[26].ToString().Trim() == "True" ? true : false;
                //employees.Privilege.isBreakDown = Data[27].ToString().Trim() == "True" ? true : false;
                //employees.Privilege.isPickKPI = Data[28].ToString().Trim() == "True" ? true : false;
                
                
               

               
            }

            return employees;
        }

        public Employee GetEmployeeByName(string Name)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeBy_Name";
            command.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = Name;

            IList<string> Data = GetSingleRow(command);
            Employee employees = null;

            if (Data != null && Data.Count > 0)
            {
                employees = new Employee();
                employees.ID = Convert.ToInt16(Data[0]);
            }

            return employees;
        }

        public Employee GetEmployeeByKPIAssignDtlID(int KPIAssignDtlID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeBy_AssignDtlID";
            command.Parameters.Add("@AssignDtlID", SqlDbType.Int).Value = KPIAssignDtlID;

            IList<string> Data = GetSingleRow(command);
            Employee employees = null;

            if (Data != null && Data.Count > 0)
            {
                employees = new Employee();
                employees.ID = Convert.ToInt16(Data[0]);
                employees.FirstName = Data[1].ToString();
                employees.LastName = Data[2].ToString();

                employees.SubBranch = new SubBranch();
                employees.SubBranch.Description = Data[3].ToString();

                employees.SubBranch.Location = new Location();
                employees.SubBranch.Location.Description = Data[4].ToString();

                employees.Department = new Department();
                employees.Department.Description = Data[5].ToString();

                employees.JobPosition = new JobPosition();
                employees.JobPosition.Position = Data[6].ToString();
                
                //employees.Department = new DepartmentController().GetDepartmentByID(Data[3] == string.Empty ? 0 : Convert.ToInt16(Data[3].ToString()));
                //employees.Section = new SectionController().GetSectionByID(Data[4] == string.Empty ? 0 : Convert.ToInt16(Data[4].ToString()));
                //employees.SubSection = new SubSectionController().GetSubSectionByID(Data[5] == string.Empty ? 0 : Convert.ToInt16(Data[5].ToString()));
                //employees.Supervisor = Convert.ToInt16(Data[6].ToString());//GetEmployeeByID(Data[6] == string.Empty ? 0 : Convert.ToInt16(Data[6].ToString()));
                //employees.JobPosition = new JobPositionController().GetJobPositionByID(Data[7] == string.Empty ? 0 : Convert.ToInt16(Data[7].ToString()));
                //employees.JobTitle = Data[8].ToString();
                //employees.Email = Data[9].ToString();
                //employees.SubBranch = new SubBranchController().GetSubBranchByID(Data[10] == string.Empty ? 0 : Convert.ToInt16(Data[10].ToString()));
                //employees.Phone = Data[11].ToString();
                //employees.Ext = Data[12].ToString();
                //employees.Password = Data[13].ToString();
                //employees.isAdmin = Convert.ToBoolean(Data[14]);
                //employees.isActive = Convert.ToBoolean(Data[15]);
                //employees.LastModifiedDate = Data[16] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[16].ToString());
                //employees.LastModifiedBy = Data[17].ToString();
                //employees.CreateDate = Data[18] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[18].ToString());
                //employees.CreateBy = Data[19].ToString();
                //employees.JoinDate = Data[20] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[20].ToString());
                //employees.Privilege = new PrivilegeController().GetPrivilegeByID(Data[21] == string.Empty ? 0 : Convert.ToInt16(Data[21].ToString()));
                //employees.BusinessGroupID = Convert.ToInt16(Data[22].ToString());

                //if (ID != Convert.ToInt32(Data[6]))

                //else
                //    employees.Supervisor = employees;
            }

            return employees;
        }

        public Employee GetEmployeeLogin(string Username, string Password)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeLogin";
            command.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = Username;
            command.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = Password;

            IList<string> Data = GetSingleRow(command);
            Employee employees = null;

            if (Data != null && Data.Count > 0)
            {
                employees = new Employee();
                employees.ID = Convert.ToInt16(Data[0]);
                employees.FirstName = Data[1].ToString();
                employees.LastName = Data[2].ToString();
            }

            return employees;
        }

        public IList<Employee> GetAssignedEmployeeByKPIID(int KPIID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAssignedEmployeeByKPIID";
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIID;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Employee p = new Employee();
                    p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                    p.FirstName = dt.Rows[i]["FirstName"].ToString();
                    p.LastName = dt.Rows[i]["LastName"].ToString();
                    p.Department = new DepartmentController().GetDepartmentByID(Convert.ToInt16(dt.Rows[i]["DepartmentID"].ToString()));
                    p.Section = new SectionController().GetSectionByID(dt.Rows[i]["SectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SectionID"].ToString()));
                    p.SubSection = new SubSectionController().GetSubSectionByID(dt.Rows[i]["SubSectionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubSectionID"].ToString()));
                    p.Supervisor = Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString());// GetEmployeeByID(dt.Rows[i]["Supervisor"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Supervisor"].ToString()));
                    p.JobPosition = new JobPositionController().GetJobPositionByID(dt.Rows[i]["JobPositionID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["JobPositionID"].ToString()));
                    p.JobTitle = dt.Rows[i]["JobTitle"].ToString();
                    p.Email = dt.Rows[i]["Email"].ToString();
                    p.SubBranch = new SubBranchController().GetSubBranchByID(dt.Rows[i]["SubBranchID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["SubBranchID"].ToString()));
                    p.Phone = dt.Rows[i]["Phone"].ToString();
                    p.Ext = dt.Rows[i]["Ext"].ToString();
                    p.Password = dt.Rows[i]["Password"].ToString();
                    p.isAdmin = Convert.ToBoolean(dt.Rows[i]["isAdmin"].ToString());
                    p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                    p.JoinDate = dt.Rows[i]["JoinDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["JoinDate"].ToString());
                    p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                    p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                    p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                    p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                    Employees.Add(p);
                }
            }

            return Employees.ToList();
        }

        public IList<Employee> GetUnderlingBySuperiorID(Employee Superior, string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetUnderlingBySuperiorID";
            command.Parameters.Add("@SUPERIORID", SqlDbType.VarChar).Value = Superior.ID;
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "EmployeeName":
                            dx.Tables[0].DefaultView.Sort = "FirstName ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment ASC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection ASC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection ASC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "DescriptionJobPosition ASC";
                            break;
                        case "JobTitle":
                            dx.Tables[0].DefaultView.Sort = "JobTitle ASC";
                            break;

                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "EmployeeName":
                            dx.Tables[0].DefaultView.Sort = "FirstName DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment DESC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection DESC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection DESC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "DescriptionJobPosition DESC";
                            break;
                        case "JobTitle":
                            dx.Tables[0].DefaultView.Sort = "JobTitle DESC";
                            break;
                    }
                    break;
            }


            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                //for (int i = 0; i < dt.Rows.Count; i++)
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();

                        
                        p.Department = new Department();
                        p.Department.Description = dt.Rows[i]["DescriptionDept"].ToString();

                        p.SubBranch = new SubBranch();
                        p.SubBranch.Description = dt.Rows[i]["DescriptionSubBranch"].ToString();

                        p.Section = new Section();
                        p.Section.Description = dt.Rows[i]["DescriptionSection"].ToString();

                        p.JobPosition = new JobPosition();
                        p.JobPosition.Position = dt.Rows[i]["DescriptionJobPosition"].ToString();

                        p.SubSection = new SubSection();
                        p.SubSection.Description = dt.Rows[i]["DescriptionSubSection"].ToString();
                       
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        p.JobTitle = dt.Rows[i]["JobTitle"].ToString();
                       
                        Employees.Add(p);
                    }
                    else
                    {
                        break;
                    }
                }
            }

            TotalData = dt.Rows.Count;
            return Employees.ToList();
        }

        public IList<Employee> GetUnderlingBySuperiorID_TotalWeightKPI(Employee Superior, string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetUnderlingBySuperiorID_TotalWeight";
            command.Parameters.Add("@SUPERIORID", SqlDbType.VarChar).Value = Superior.ID;
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "EmployeeName":
                            dx.Tables[0].DefaultView.Sort = "FirstName ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment ASC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection ASC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection ASC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "DescriptionJobPosition ASC";
                            break;
                        case "JobTitle":
                            dx.Tables[0].DefaultView.Sort = "JobTitle ASC";
                            break;

                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "EmployeeName":
                            dx.Tables[0].DefaultView.Sort = "FirstName DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment DESC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection DESC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection DESC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "DescriptionJobPosition DESC";
                            break;
                        case "JobTitle":
                            dx.Tables[0].DefaultView.Sort = "JobTitle DESC";
                            break;
                    }
                    break;
            }


            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                //for (int i = 0; i < dt.Rows.Count; i++)
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();


                        p.Department = new Department();
                        p.Department.Description = dt.Rows[i]["DescriptionDept"].ToString();

                        p.SubBranch = new SubBranch();
                        p.SubBranch.Description = dt.Rows[i]["DescriptionSubBranch"].ToString();

                        p.Section = new Section();
                        p.Section.Description = dt.Rows[i]["DescriptionSection"].ToString();

                        p.JobPosition = new JobPosition();
                        p.JobPosition.Position = dt.Rows[i]["DescriptionJobPosition"].ToString();

                        p.SubSection = new SubSection();
                        p.SubSection.Description = dt.Rows[i]["DescriptionSubSection"].ToString();

                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());

                        p.JobTitle = dt.Rows[i]["JobTitle"].ToString();

                        p.TotalWeight = dt.Rows[i]["TotalWeight"].ToString();

                        Employees.Add(p);
                    }
                    else
                    {
                        break;
                    }
                }
            }

            TotalData = dt.Rows.Count;
            return Employees.ToList();
        }

        public IList<Employee> GetUnderlingBySuperiorIDAdmin(int RgmTypeID, int RgmID, int BranchID, int SubBranchID, int DepartmentID, int SectionID,
        string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetUnderlingBySuperiorID_Admin";
            command.Parameters.Add("@RgmTypeID", SqlDbType.VarChar).Value = RgmTypeID;
            command.Parameters.Add("@RgmID", SqlDbType.VarChar).Value = RgmID;
            //command.Parameters.Add("@RgmTypeID", SqlDbType.VarChar).Value = RgmTypeID;
            command.Parameters.Add("@BranchID", SqlDbType.VarChar).Value = BranchID;
            command.Parameters.Add("@SubBranchID", SqlDbType.VarChar).Value = SubBranchID;
            command.Parameters.Add("@DepartmentID", SqlDbType.VarChar).Value = DepartmentID;
            command.Parameters.Add("@SectionID", SqlDbType.VarChar).Value = SectionID;
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;

            DataSet dx = GetTable(command);

            List<Employee> Employees = new List<Employee>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "EmployeeName":
                            dx.Tables[0].DefaultView.Sort = "FirstName ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment ASC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection ASC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection ASC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "DescriptionJobPosition ASC";
                            break;
                        case "JobTitle":
                            dx.Tables[0].DefaultView.Sort = "JobTitle ASC";
                            break;

                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "EmployeeName":
                            dx.Tables[0].DefaultView.Sort = "FirstName DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "Departemen":
                            dx.Tables[0].DefaultView.Sort = "DescriptionDepartment DESC";
                            break;
                        case "Section":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSection DESC";
                            break;
                        case "SubSection":
                            dx.Tables[0].DefaultView.Sort = "DescriptionSubSection DESC";
                            break;
                        case "JobPosition":
                            dx.Tables[0].DefaultView.Sort = "DescriptionJobPosition DESC";
                            break;
                        case "JobTitle":
                            dx.Tables[0].DefaultView.Sort = "JobTitle DESC";
                            break;
                    }
                    break;
            }


            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                //for (int i = 0; i < dt.Rows.Count; i++)
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Employee p = new Employee();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        p.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.LastName = dt.Rows[i]["LastName"].ToString();

                        
                        p.Department = new Department();
                        p.Department.Description = dt.Rows[i]["DescriptionDept"].ToString();

                        p.SubBranch = new SubBranch();
                        p.SubBranch.Description = dt.Rows[i]["DescriptionSubBranch"].ToString();

                        p.Section = new Section();
                        p.Section.Description = dt.Rows[i]["DescriptionSection"].ToString();

                        p.JobPosition = new JobPosition();
                        p.JobPosition.Position = dt.Rows[i]["DescriptionJobPosition"].ToString();

                        p.SubSection = new SubSection();
                        p.SubSection.Description = dt.Rows[i]["DescriptionSubSection"].ToString();

                        p.JobTitle = dt.Rows[i]["JobTitle"].ToString();
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        Employees.Add(p);
                    }
                    else
                    {
                        break;
                    }
                }
            }

            TotalData = dt.Rows.Count;
            return Employees.ToList();
        }
    }
}